package com.roc.web.action.terminal;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.PaginationBaseAction;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
/**
 * 终端sn号段统计
* @Title: TerminalSnBatchStatisticsSearchCondition.java 
* @Package com.roc.web.action.terminal 
* @Description: TODO 
* @author dk
* @version V1.0
 */
public class TerminalSnBatchStatisticsSearchCondition extends BaseSearchCondition {	
	
	private String  agentId;// 一级代理商
	private String  agentName;// 一级代理商名称
	private String obj;
	private String orgCode;// 机构号

	private String  serialNum ;// SN
	private String  posBrand ;// 厂商
	private String  startBrandDate ;// 绑定开始时间(年月日)
	private String  endBrandDate ;// 绑定结束时间(年月日)
	
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
	/*
		if (Constance.isNotEmpty(agentId))
		{
			filedBeanList.add(new WhereHQLFieldBean("t1.ONEAGENTID", agentId.trim(), "=", false));
		}*/
		if (Constance.isNotEmpty(agentName))
		{
			filedBeanList.add(new WhereHQLFieldBean("c.oneagentname", agentName.trim(), "=", false));
		}
		// SN 	
		if (Constance.isNotEmpty(serialNum))
		{
			filedBeanList.add(new WhereHQLFieldBean("c.device_cd", serialNum.trim(), "=", false));
		}
		// 厂商
		if (Constance.isNotEmpty(posBrand) && !posBrand.equals("-1")) {
			filedBeanList.add(new WhereHQLFieldBean("c.tmnl_brand", posBrand.trim(), "=", false));
		}	
		// 绑定日期 
		if (Constance.isNotEmpty(startBrandDate) && Constance.isNotEmpty(endBrandDate)) {
			String startDate = startBrandDate.replace("-", "");
			String endDate = endBrandDate.replace("-", "");
			
			filedBeanList.add(new WhereHQLFieldBean(
					"c.brand_date", startDate, ">=",
					false));
			filedBeanList
					.add(new WhereHQLFieldBean(
							"c.brand_date", endDate,
							"<=", false));
		}
		return filedBeanList;
	}

	@Override
	protected String getRowCountHQL() {
		StringBuffer sb=new StringBuffer();
	/*	sb.append(" select count(1) ");
		sb.append("from (select tmp.real_name,");
		sb.append("(select o1.real_name from t_operators o1 where to_char(o1.id) = tmp.companyid) companyname,");
		sb.append("(select o2.real_name from t_operators o2 where to_char(o2.id) = tmp.oneagentid) oneagentname,");
		sb.append("t.device_cd,t.tmnl_brand,to_char(t.rec_upd_ts,'yyyyMMdd') brand_date,t.term_id,t.mcht_cd ");
		sb.append("from tbl_term_inf t,tbl_mcht_inf m,");
		sb.append("(select to_char(s.id) tid, s.real_name, s.companyid, s.oneagentid from t_operators s start with id = "+obj);
		sb.append(" connect by prior s.id = s.supri_id and s.role_type = '03') tmp ");
		sb.append("where t.mcht_cd = m.mcht_cd and t.belong_sub = ").append(orgCode) .append(" and instr(t.device_cd, 'YJ') <= 0 and m.ext1 = tmp.tid ) c where 1=1 ");
		*/
		sb.append( " select count(1) from (with ope as " );
		sb.append( "  (select to_char(s.id) tid, " );
		sb.append( "          s.real_name, " );
		sb.append( "          (select o1.real_name " );
		sb.append( "             from t_operators o1 " );
		sb.append( "            where to_char(o1.id) = s.companyid) companyname, " );
		sb.append( "          (select o2.real_name " );
		sb.append( "             from t_operators o2 " );
		sb.append( "            where to_char(o2.id) = s.oneagentid) oneagentname, " );
		sb.append( "            s.oneagentid " );
		sb.append( "     from t_operators s " );
		sb.append( "    start with id = " + obj );
		sb.append( "   connect by prior s.id = s.supri_id " );
		sb.append( "          and s.role_type = '03') " );
		sb.append( " select ope.real_name, " );
		sb.append( "        ope.companyname, " );
		sb.append( "        ope.oneagentname, " );
		sb.append( "        t.device_cd, " );
		sb.append( "        t.tmnl_brand, " );
		sb.append( "        to_char(t.rec_upd_ts, 'yyyyMMdd') brand_date, " );
		sb.append( "        t.term_id, " );
		sb.append( "        t.mcht_cd, " );
		sb.append( "        ope.oneagentid " );
		sb.append( "   from tbl_term_inf t, tbl_mcht_inf m, ope ope " );
		sb.append( "  where t.mcht_cd = m.mcht_cd " );
		sb.append( "    and t.belong_sub = " + orgCode );
		sb.append( "    and instr(t.device_cd, 'YJ') <= 0 " );
		sb.append( "    and m.ext1 = ope.tid) c where 1 = 1 " );


		
		System.out.println(sb.toString());
		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb=new StringBuffer();
/*		sb.append(" select * from( ");
		sb.append("select tmp.real_name,");
		sb.append("(select o1.real_name from t_operators o1 where to_char(o1.id) = tmp.companyid) companyname,");
		sb.append("(select o2.real_name from t_operators o2 where to_char(o2.id) = tmp.oneagentid) oneagentname,");
		sb.append("t.device_cd,t.tmnl_brand,to_char(t.rec_upd_ts,'yyyyMMdd') brand_date,t.term_id,t.mcht_cd ,tmp.oneagentid ");
		sb.append("from tbl_term_inf t,tbl_mcht_inf m,");
		sb.append("(select to_char(s.id) tid, s.real_name, s.companyid, s.oneagentid from t_operators s start with id = "+obj );
		sb.append(" connect by prior s.id = s.supri_id and s.role_type = '03') tmp ");
		sb.append("where t.mcht_cd = m.mcht_cd and t.belong_sub = ").append(orgCode) .append(" and instr(t.device_cd, 'YJ') <= 0 and m.ext1 = tmp.tid ) c where 1=1 ");
		*/
		sb.append( " select * from ( with ope as " );
		sb.append( "  (select to_char(s.id) tid, " );
		sb.append( "          s.real_name, " );
		sb.append( "          (select o1.real_name " );
		sb.append( "             from t_operators o1 " );
		sb.append( "            where to_char(o1.id) = s.companyid) companyname, " );
		sb.append( "          (select o2.real_name " );
		sb.append( "             from t_operators o2 " );
		sb.append( "            where to_char(o2.id) = s.oneagentid) oneagentname, " );
		sb.append( "            s.oneagentid " );
		sb.append( "     from t_operators s " );
		sb.append( "    start with id = " + obj );
		sb.append( "   connect by prior s.id = s.supri_id " );
		sb.append( "          and s.role_type = '03') " );
		sb.append( " select ope.real_name, " );
		sb.append( "        ope.companyname, " );
		sb.append( "        ope.oneagentname, " );
		sb.append( "        t.device_cd, " );
		sb.append( "        t.tmnl_brand, " );
		sb.append( "        to_char(t.rec_upd_ts, 'yyyyMMdd') brand_date, " );
		sb.append( "        t.term_id, " );
		sb.append( "        t.mcht_cd, " );
		sb.append( "        ope.oneagentid " );
		sb.append( "   from tbl_term_inf t, tbl_mcht_inf m, ope ope " );
		sb.append( "  where t.mcht_cd = m.mcht_cd " );
		sb.append( "    and t.belong_sub = " + orgCode );
		sb.append( "    and instr(t.device_cd, 'YJ') <= 0 " );
		sb.append( "    and m.ext1 = ope.tid) c where 1 = 1 " );

		//System.out.println(sb.toString());
		return sb.toString();
	}

	@Override
	protected String getOrderPartOfHQL() {
		return " order by c.brand_date desc ";
	}

	public String getAgentId() {
		return agentId;
	}

	public void setAgentId(String agentId) {
		this.agentId = agentId;
	}

	public String getObj() {
		return obj;
	}

	public void setObj(String obj) {
		this.obj = obj;
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getAgentName() {
		return agentName;
	}

	public void setAgentName(String agentName) {
		this.agentName = agentName;
	}

	public String getSerialNum() {
		return serialNum;
	}

	public void setSerialNum(String serialNum) {
		this.serialNum = serialNum;
	}

	public String getPosBrand() {
		return posBrand;
	}

	public void setPosBrand(String posBrand) {
		this.posBrand = posBrand;
	}

	public String getStartBrandDate() {
		return startBrandDate;
	}

	public void setStartBrandDate(String startBrandDate) {
		this.startBrandDate = startBrandDate;
	}

	public String getEndBrandDate() {
		return endBrandDate;
	}

	public void setEndBrandDate(String endBrandDate) {
		this.endBrandDate = endBrandDate;
	}
	
}
